<?php

/**
 * Class for work with database table "accounts"
 * 
 * @version 2012-10-16
 * @package Db model
 * @subpackage accounts
 * @uses Db_Table
 * @category Db model
 */


class MembersModel extends Db_Table{

	/**
     * __construct
     * This construct function connects to the table "accounts".
     *
     * @version 2012-10-10
     * @uses parent::__construct('accounts')
     * @return object
     */
	function __construct()
	{
		parent::__construct("accounts");
	}
    
	 /**
    * getPersonInfo
    * This function returns data about a person using its ID.
    *
    * @version 2012-10-18 
    * @param int $id (account id)
    * @return array row
    * @access public
    */
	public function getPersonInfo($id)
    {
		// Here local values are declared 
		$map = array(
			'id'                   => 'a.id',
			'fname'                => 'a.fname',
			'lname'                => 'a.lname',
			'birthday'             => 'a.birthday',
			'phone'                => 'a.phone',
			'email'                => 'a.email',
			'cdate'                => 'a.cdate',
			'sex'              	   => 'a.sex',
			'country'              => 'a.country',
			'state'                => 'a.state',
			'city'                 => 'a.city',
			'zipcode'              => 'a.zipcode',
			'address'              => 'a.address',
			'status'			   => 'a.status',
			'cnt_my_complaints'    => '(SELECT COUNT(*) FROM complaints AS cmpl WHERE a.id = cmpl.claimant_id)',
			'cnt_my_comments'      => '(SELECT COUNT(*) FROM comments  AS cmm WHERE a.id = cmm.account_id)',
			'cnt_consumer_comments' => "(SELECT COUNT(*) FROM comments AS cmm "
                                    ." JOIN complaints AS cmpl ON cmm.complaint_id = cmpl.id "
                                    ." JOIN accounts AS a2 ON cmm.account_id = a2.id "
                                    ." JOIN companies AS cmpn ON cmpn.id = cmpl.defendant_id "
                                    ." WHERE cmpl.claimant_id  =  a.id AND cmpl.status <> 'deleted' "
                                    ." AND cmm.status NOT IN ('deleted','pending') AND a2.status NOT IN ('deleted','pending') AND cmpn.status <> 'deleted') "
			 ,
			 'cnt_comments'         =>
				 "(SELECT COUNT(*) FROM comments AS cmm "
				 ." JOIN complaints AS cmpl ON cmm.complaint_id = cmpl.id "
				 ." JOIN companies as com ON cmpl.defendant_id = com.id"
				 ." WHERE cmpl.claimant_id = a.id AND cmm.status NOT IN ('deleted') AND cmm.is_anonim = 0 AND com.status <>'deleted' AND cmpl.status <> 'deleted')"
			 ,
			 'cnt_complaints'			=>
				 "(SELECT COUNT(*) FROM complaints as cmpl "
				 ." INNER JOIN companies AS com ON(cmpl.defendant_id = com.id) "
				 ." WHERE cmpl.status <>'deleted' AND com.status <>'deleted' AND cmpl.is_anonim = 0 AND cmpl.claimant_id = a.id)",
			'cnt_followings'       => '(SELECT COUNT(*) FROM following_lists AS  f WHERE a.id = f.account_id )',
			'balance'              => '(SELECT SUM(refunded) AS balance FROM complaints AS cmpl WHERE cmpl.claimant_id = a.id)',
		);
		// Here an SQL query is formed
		$sql = "SELECT "
            .$this->_joinFields($map)
            ." FROM accounts AS a "
			." WHERE a.id = ".$id;
		return $this->db->getRow($sql);
	}   

    
    /**
     * getNewConsumerComments
     * This function returns comments on the member's complaints since last log-in.
     *
     * @version 2012-10-18
     * @param int $id
     * @param string $date
     * @return array row
     * @access public
     */
    public function getNewConsumerComments($id, $date)
    {
        $sql = "SELECT COUNT(*) FROM comments AS cmm "
            ." JOIN complaints AS cmpl ON cmm.complaint_id = cmpl.id "
            ." JOIN accounts AS a ON cmm.account_id = a.id "
            ." JOIN companies AS cmpn ON cmpn.id = cmpl.defendant_id "
            ." WHERE cmpl.claimant_id  =  ".$id." AND cmpl.status <> 'deleted' "
            ." AND cmm.status NOT IN ('deleted','pending') AND a.status NOT IN ('deleted','pending') AND cmpn.status <> 'deleted' "
            ." AND cmm.cdate > '"._dformat($date, _cfg('format.date.sql'))."'";
        return $this->db->getField($sql);
    }
	
	/**
	* getCompanyList
    * This function returns an array with the list of persons. 
    * 
    * @version 2012-11-09 
    * @param array $where
    * @param array $sort
    * @param int $offset
    * @param int $limit
    * @return array rows
    * @access public
    */
	public function getMembersList($where=array(),$pager = 100000, $sort=array()) {
		// Here local values are declared 
		$map = array(
			'id'			=>	'a.id',
			'cdate'			=>	'a.cdate',
			'utype'			=>	'a.utype',
			'status'			=>	'a.status',
			'phone'			=>	'a.phone',
			'name'			=>	'CONCAT(a.fname," ",a.lname)',
			'email'			=>	'a.email',
			'search'			=>	'CONCAT(a.fname," ",a.lname," ",a.email," ",a.phone," ",a.status)',
			'cnt_complaints'=>	"(
				SELECT 	COUNT(*)
					FROM complaints as cmpl
					INNER JOIN companies as com ON(cmpl.defendant_id = com.id)
				WHERE cmpl.status <> 'deleted'
					AND com.status <> 'deleted'
					AND cmpl.claimant_id = a.id
			)",
			'cnt_comments'	=>	"(
				SELECT COUNT(*) AS cnt_comments
						FROM	comments as com
						INNER JOIN complaints as c ON(com.complaint_id = c.id)
						INNER JOIN companies as cmp ON (cmp.id = c.defendant_id)
				WHERE
						com.status NOT IN('deleted') 
						AND a.id = com.account_id
						AND c.status NOT IN('deleted')
						AND cmp.status NOT IN('deleted')
			)"
		);
		
		// Here an SQL query is formed
		$sql = 'SELECT COUNT(*) FROM accounts AS a ' 
			.'WHERE '.$this->_parseWhere($where, $map);
        $cnt = $this->db->getField($sql);
		if (!$cnt)
            return array();
		if ($pager){
            if (is_object($pager))
                $limit = $pager->init($cnt);
            else 
                $limit = intVal($pager);
				}
				
		// Here an SQL query is formed
		$sql = "SELECT "
         .$this->_joinFields($map)
         ." FROM accounts as a "
			."WHERE ".$this->_parseWhere($where, $map)." "
         .$this->_parseOrder($sort)
         ." LIMIT ".$limit."";
		return $this->db->getRows($sql);
	}	
}    
